drop table if exists `machine_software`
GO
CREATE TABLE `machine_software` (
`machine_software_id` int(11) AUTO_INCREMENT NOT NULL,
`computing_machine_id`	int(11) NOT NULL,
`software_id`       	int(11) NOT NULL,
PRIMARY KEY(machine_software_id)
)
GO
DROP TABLE if exists `computing_machine`
GO
CREATE TABLE computing_machine (
	`machine_id`             	int(11) AUTO_INCREMENT NOT NULL,
	machine_cloud_id        	varchar(255) NULL,
	machine_name           	varchar(255) NOT NULL,
	memory                	int(11) NULL,
	operating_system       	varchar(255) NOT NULL,
	operating_system_version	varchar(255) NULL,
	cloud_lab_id        	int(11) NULL,
    PRIMARY KEY(machine_id)
	)
GO

DROP TABLE if exists `cloud_lab`
go
CREATE TABLE `cloud_lab` (
`lab_id` int(11) AUTO_INCREMENT NOT NULL,
`lab_name` varchar(255) NOT NULL,
`lab_status`	varchar(255) NULL,
PRIMARY KEY(lab_id)
)
go

drop table if exists software
GO
CREATE TABLE software (
	software_id     	int(11) AUTO_INCREMENT NOT NULL,
	software_name   	varchar(255) NOT NULL,
	software_version	varchar(255) NULL,
    PRIMARY KEY(software_id)
	)
GO

CREATE INDEX ix_computingmachine_cloudLabId
	ON computing_machine(cloud_lab_id)
GO
CREATE INDEX ix_machinesoftware_softwareId
	ON machine_software(software_id)
GO
CREATE INDEX ix_machinesoftware_computingMachineId
	ON machine_software(computing_machine_id)
GO

ALTER TABLE computing_machine
	ADD CONSTRAINT FK_computingmachine_cloudlab
	FOREIGN KEY(cloud_lab_id)
	REFERENCES cloud_lab(lab_id)
GO
ALTER TABLE machine_software
	ADD CONSTRAINT FK_machinesoftware_computingmachine
	FOREIGN KEY(computing_machine_id)
	REFERENCES computing_machine(machine_id)
GO
ALTER TABLE machine_software
	ADD CONSTRAINT FK_machinesoftware_software
	FOREIGN KEY(software_id)
	REFERENCES software(software_id)
GO


insert into software(software_name, software_version) values('jdk1.6', '1.6')
go
insert into software(software_name, software_version) values('mysql server', '5.0')
go
insert into software(software_name, software_version) values('eclipse', '3.2')
go
insert into software(software_name, software_version) values('dbvisualizer', '8.0')
go

--cloud lab sample data
insert into cloud_lab(lab_name, lab_status) values('lab2', 'deployed')
go
insert into cloud_lab(lab_name, lab_status) values('lab3', 'deployed')
go
insert into cloud_lab(lab_name, lab_status) values('lab4', 'deployed')
go
insert into cloud_lab(lab_name, lab_status) values('lab5', 'deployed')
go
insert into cloud_lab(lab_name, lab_status) values('lab6', 'deployed')
go
insert into cloud_lab(lab_name, lab_status) values('lab7', 'deployed')
go

insert into computing_machine(machine_cloud_id, machine_name, operating_system, memory,cloud_lab_id) values('aws-1234', 'Sample1', 'Linux 7.0', 2, 1)
go
insert into computing_machine(machine_cloud_id, machine_name, operating_system, memory,cloud_lab_id) values('aws-1234', 'Sample2', 'Linux 7.0', 2, 1)
go
    insert into computing_machine(machine_cloud_id, machine_name, operating_system, memory,cloud_lab_id) values('aws-1234', 'Sample3', 'Linux 7.0', 2, 1)
go
insert into computing_machine(machine_cloud_id, machine_name, operating_system, memory,cloud_lab_id) values('aws-1234', 'Sample4', 'Linux 7.0', 2, 2)
go
insert into computing_machine(machine_cloud_id, machine_name, operating_system, memory,cloud_lab_id) values('aws-1234', 'Sample5', 'Linux 7.0', 2, 2)
go